﻿

CREATE PROCEDURE sec__InsertTriggers
AS

CREATE TABLE #tracestatus (PKTABLE_QUALIFIER	sysname	,PKTABLE_OWNER	sysname	,PKTABLE_NAME	sysname	,PKCOLUMN_NAME	sysname	,FKTABLE_QUALIFIER	sysname	,FKTABLE_OWNER	sysname	,FKTABLE_NAME	sysname	,FKCOLUMN_NAME	varchar(32)	,KEY_SEQ	smallint	,UPDATE_RULE	smallint	,DELETE_RULE	smallint	,FK_NAME	sysname	,PK_NAME	sysname,DIFERRABILITY int)

INSERT INTO #tracestatus EXEC ('exec sp_fkeys ''t_Applicants''')

DECLARE cur CURSOR
READ_ONLY
FOR SELECT FKTABLE_NAME FROM #tracestatus-- WHERE FKTABLE_NAME = 't_References'

DECLARE @TableName sysname,@ApplIDDesc varchar(512)

OPEN cur

FETCH NEXT FROM cur INTO @TableName
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN
		SET @ApplIDDesc = (SELECT CONVERT(varchar(512),Value)FROM ::fn_listextendedproperty ('MS_Description', 'user', 'dbo', 'table', @TableName, 'column', 'ApplicantID'))
		PRINT dbo.sprintf1('sec__InsertTriggers<>@TableName:$0,@ApplIDDesc:$1',@TableName,@ApplIDDesc)
		IF NOT ISNULL(@ApplIDDesc,'') LIKE '%nosec%' BEGIN
			--EXEC Sec__CreateSecurityTrigger @TableName,'INSERT'
			EXEC Sec__CreateSecurityTrigger @TableName,'UPDATE'
			EXEC Sec__CreateSecurityTrigger @TableName,'DELETE'
		END
	END
	FETCH NEXT FROM cur INTO @TableName
END

EXEC Sec__CreateSecurityTrigger 't_Applicants','UPDATE','IF Update(MenuID) RETURN'

CLOSE cur
DEALLOCATE cur
DROP TABLE #tracestatus
--exec sp_fkeys 't_Applicants'
--exec  sp_helpconstraint 't_Applicants'
--exec sp_helptrigger 't_Applicants'


